﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace customer.dao.V2
{
    public class MySqlDBEngine : DBEngineBase
    {
        public MySqlDBEngine(string connectionString)
        {
            this.ConnectionString = connectionString;
        }

        public override GetResult Get(string sql)
        {
            GetResult result = new GetResult();

            try
            {
                using (MySqlConnection conn = new MySqlConnection(this.ConnectionString))
                {
                    conn.Open();

                    MySqlDataAdapter adapter = null;

                    DataTable dtResult = new DataTable();


                    adapter = new MySqlDataAdapter(sql, conn);

                    adapter.Fill(dtResult);

                    List<Dictionary<string, object>> items = DATA_Util.DataTable2Dictionary(dtResult);

                    if (items.Count > 0)
                        result.Item = items[0];

                }

                result.Success = true;
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Message = ex.ToString();
            }

            return result;
        }

        public override QueryResult Query(string sql, string pk, int pageIndex, int pageSize)
        {
            QueryResult result = new QueryResult();

            try
            {
                string totalSQL = "select count(*) from ( " + sql + " ) as _DATATABLE ";

                using (MySqlConnection conn = new MySqlConnection(this.ConnectionString))
                {
                    conn.Open();

                    MySqlDataAdapter adapter = null;

                    DataTable dtResult = new DataTable();

                    if (pageIndex > 0 && pageSize > 0)
                    {
                        using (MySqlCommand cmd = conn.CreateCommand())
                        {   
                            cmd.CommandText = totalSQL;
                            result.Total = int.Parse(cmd.ExecuteScalar().ToString());
                        }

                        int firstIndex = (pageIndex - 1) * pageSize;

                        string pageSQL = "select * from(" + sql + ") as  _DATATABLE limit "+ firstIndex +"," + pageSize;

                        adapter = new MySqlDataAdapter(pageSQL, conn);

                        adapter.Fill(dtResult);

                        result.PageNo = pageIndex;
                    }
                    else
                    {   
                        adapter = new MySqlDataAdapter(sql, conn);

                        adapter.Fill(dtResult);

                        result.Total = dtResult.Rows.Count;
                    }

                    result.Items = DATA_Util.DataTable2Dictionary(dtResult).ToArray();

                }

                result.Success = true;
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Message = ex.ToString();
            }

            return result;
        }

        public override ScaleResult Scale(string sql)
        {
            ScaleResult result = new ScaleResult();

            try
            {
                using (MySqlConnection conn = new MySqlConnection(this.ConnectionString))
                {
                    conn.Open();

                    using (MySqlCommand cmd = conn.CreateCommand())
                    {   
                        cmd.CommandText = sql;
                        result.Scalar = cmd.ExecuteScalar();
                    }
                }

                result.Success = true;
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Message = ex.ToString();
            }

            return result;
        }

       

        public override WriteResult Write(string sql)
        {
            WriteResult result = new WriteResult();
            try
            {
                using (MySqlConnection conn = new MySqlConnection(this.ConnectionString))
                {
                    conn.Open();

                    using (MySqlCommand cmd = conn.CreateCommand())
                    {   
                        cmd.CommandText = sql;
                        result.rows = cmd.ExecuteNonQuery();
                    }
                }
                result.Success = true;
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Message = ex.ToString();
            }
            return result;
        }

        public override DataResult Test()
        {
            DataResult result = new DataResult();
            try
            {
                using (MySqlConnection conn = new MySqlConnection(this.ConnectionString))
                {
                    conn.Open();

                }
                result.Success = true;
            }
            catch (Exception ex)
            {
                result.Success = false;
                result.Message = ex.ToString();
            }
            return result;
        }

    }
}
